package com.elite.groovy.callback

import com.alibaba.fastjson.JSONArray
import com.alibaba.fastjson.JSONObject
import com.elite.groovy.kh.KHDBConfig
import com.elite.groovy.kh.KHUtil
import com.elite.groovy.util.EliteBean
import com.elite.groovy.util.IpAddressUtil
import com.wisdge.dataservice.Result
import org.apache.commons.lang.StringEscapeUtils
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse

/**
 * @projectName: ngsapitask-v3
 * @package: cn.com
 * @className: chatproxy
 * @author: LiJT
 * @description: 网聊机器人接口
 * @date: 2022-09-29 13:21
 * @version: 1.0
 */

class apiChatSoBotProxy implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiChatSoBotProxy.class);
    private static JSONObject resHuman = new JSONObject();
    private static String[] humanKeys = "人工;转人工".split(";");
    private static String[] directKeys = "转人工服务".split(";");
    private static String rclient_Lang = "简体";

    String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");
        long startTime = System.currentTimeMillis();
        String outData = "";

        // 调用IP过滤
        if (KHDBConfig.getParam(eliteBean, "#APIIP") == "1" && !KHUtil.getRemoteHost(request)) {
            outData = "API被非法IP调用-Request Not Allow!" + IpAddressUtil.getIpAddress(request);
            log.warn(outData);
            return JSONObject.toJSONString(new Result(Result.ERROR, outData));
        }

        String getMethod = request.getMethod();
        log.debug("HTTP请求调用类型 getMethod:" + getMethod);
        if (getMethod != "POST" && getMethod != "GET") {
            outData = "HTTP请求调用类型错误，接口调用失败：" + getMethod;
            log.error(outData);
            return JSONObject.toJSONString(new Result(Result.ERROR, outData));
        }

        // 获取所有Header
        Map<String, String> mapHeader = new HashMap<String, String>();
        Enumeration headerNames = request.getHeaderNames();
        while (headerNames.hasMoreElements()) {
            String key = (String) headerNames.nextElement();
            String value = request.getHeader(key);
            log.debug("Call Header-" + key + ":" + value);

            mapHeader.put(key, value);
        }

        // 获取所有参数名
        Map<String, String> mapParam = new HashMap<String, String>();
        Enumeration ep = request.getParameterNames();
        while (ep.hasMoreElements()) {
            String key = (String) ep.nextElement();
            String value = request.getParameter(key);
            log.debug("Call Parameter-" + key + ":" + value);
            mapParam.put(key, value);

        }

        // 获取数据源List<Map<String, Object>>
        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());

        // 获取调用数据
        String Syncpram = "";
        if (getMethod.equals("GET")) {
            Syncpram = request.getQueryString();
            log.info("接收调用GET参数信息：" + Syncpram);
        } else if (getMethod.equals("POST")) {
            InputStreamReader reader = new InputStreamReader(request.getInputStream(), "UTF-8");
            BufferedReader iny = new BufferedReader(reader);
            String line;
            while ((line = iny.readLine()) != null) {
                Syncpram += line;
            }
            log.info("接收调用POST报文信息：" + System.getProperty("line.separator") + Syncpram);
        }

        // 初始化默认返回
        resHuman = JSONObject.parseObject(KHDBConfig.getParamConfig(eliteBean, "ChatRobotHuman_Json"));
        humanKeys = KHDBConfig.getParamConfig(eliteBean, "ChatRobotHuman_Keys").split(";");
        directKeys = KHDBConfig.getParamConfig(eliteBean, "ChatRobotHuman_directKeys").split(";");

        try {
            // 解析请求报文
            JSONObject callJson = JSONObject.parseObject(Syncpram);
            JSONObject sobotBody = callJson.getJSONObject("data");

            // 解析用户问题
            String question = sobotBody.getString("question");

            if (KHUtil.isNull(question)) {
                outData = "未解析到用户问题-接口调用失败question：" + question;
                log.error(outData);
                return JSONObject.toJSONString(new Result(Result.ERROR, outData));

            } else {
                // 处理获取本地快捷话术问题&答案
                try {
                    // 直接转人工-暂时关闭入口,匹配到对应关键字直接转人工
                    if (Arrays.asList(directKeys).contains(question)) {
                        return doHumanQueue(jt, callJson, resHuman);
                    }

                    // 间接转人工-匹配到对应关键字询问是否转人工,如果存在产品列表带出列表给客户选择-如果问题是订单#开头则返回客户近期的订单并转人工
                    if (Arrays.asList(humanKeys).contains(question) || question.startsWith("订单#")) {
                        outData = doHumanQueue(jt, callJson, doProductJson(jt, callJson)).toString();
                        log.debug("处理获取订单 doProductJson返回结果：" + outData);
                        if (!KHUtil.isNull(outData)) {
                            return outData;
                        }
                    }

                    // 导航&问题
                    // outData = doRobotJson(jt, sobotBody).toString(); // 快捷话术模式
                    JSONObject robotJson = doHumanQueue(jt, callJson, getLocalBot(eliteBean, jt, encode(sobotBody.getString("question"), false), String.valueOf(callJson.getInteger("queueID")), rclient_Lang, 1)); // 自动问答配置模式
                    if (robotJson.isEmpty()) {
                        outData = doHumanQueue(jt, callJson, getLocalBot(eliteBean, jt, encode(sobotBody.getString("question"), false), String.valueOf(callJson.getInteger("queueID")), rclient_Lang, -1)).toString();
                        log.debug("处理获取本地快捷话术问题&答案 返回无答案结果：" + outData);
                        return outData;
                    } else {
                        outData = robotJson.toString();
                        log.debug("处理获取本地快捷话术问题&答案 getRobotJson返回结果：" + outData);
                        return outData;
                    }
                } catch (Exception e) {
                    outData = "获取本地快捷话术问题&答案异常：" + e.getMessage();
                    log.error(outData, e);
                    return JSONObject.toJSONString(new Result(Result.ERROR, outData));

                }
            }

        } catch (Exception e) {
            outData = "HTTP请求调用错误，接口处理异常：" + e.getMessage();
            log.error(outData, e);
            return JSONObject.toJSONString(new Result(Result.ERROR, outData));

        }
    }

    // 获取本地维护机器人
    static JSONObject getLocalBot(EliteBean eliteBean, JdbcTemplate jt, String question, String queue_ID, String client_Lang, int Ques_Type) {

        JSONObject resultBotAns = new JSONObject();

        // 正常检索答案时判断是否是直接转人工关键问题
        if (Ques_Type == 1) {
            resultBotAns = getLocalStaff(eliteBean, jt, question, queue_ID, client_Lang);
        }

        // 判断转人工检查是否有返回-如果有说明要转人工
        if (resultBotAns.isEmpty()) {
            try {

                String ReAnswerInfo = "";
                String RecommendInfo = "";
                log.debug("Proxy获取本地入参 [Ques_Type:" + Ques_Type + "] 问题语言:" + rclient_Lang + " 问题:" + question + " 队列:" + queue_ID);
                List<Map<String, Object>> selectResult = null;
                String selectSQL = "";

                Ques_Type = question.startsWith("#问候语#") ? 0 : Ques_Type;

                if (Ques_Type == 1) {
                    selectSQL = '''Select R1.Ques_Guid, R2.Rec_Guid, R2.Question, R2.Answer, R2.Answer_Lang, R2.Answer_Type, R2.Html_Answer_Path
                                  From Kh_Robot_Question R1
                                  Left Join Kh_Robot_Question_Lang R2
                                    On R1.Ques_Guid = R2.Ques_Guid
                                 Where Now() Between R1.Starttime And R1.Endtime
                                   And (R2.Question Like Concat('%', ?, '%') Or R2.Answer Like Concat('%', ?, '%'))
                                   And R2.Answer_Lang = ?
                                   And R1.Ques_Type = ?
                                   And (R1.Queue_Id = ? Or R1.Queue_Id Is Null Or R1.Queue_Id = '')
                                 Order By Case
                                            When R1.Queue_Id = ? And R2.Question Like Concat('%', ?, '%') Then
                                             1
                                            When R1.Queue_Id = ? And R2.Answer Like Concat('%', ?, '%') Then
                                             2
                                            When (R1.Queue_Id Is Null Or R1.Queue_Id = '') And R2.Question Like Concat('%', ?, '%') Then
                                             3
                                            When (R1.Queue_Id Is Null Or R1.Queue_Id = '') And R2.Answer Like Concat('%', ?, '%') Then
                                             4
                                            Else
                                             99
                                          End,
                                          R2.Createtime Desc Limit 1''';
                    selectResult = jt.queryForList(selectSQL, question, question, rclient_Lang, Ques_Type, queue_ID, queue_ID, question, queue_ID, question, question, question);

                } else {
                    selectSQL = '''Select R1.Ques_Guid, R2.Rec_Guid, R2.Question, R2.Answer, R2.Answer_Lang, R2.Answer_Type, R2.Html_Answer_Path
                                          From Kh_Robot_Question R1
                                          Left Join Kh_Robot_Question_Lang R2
                                            On R1.Ques_Guid = R2.Ques_Guid
                                         Where Now() Between R1.Starttime And R1.Endtime
                                           And R2.Answer_Lang = ?
                                           And R1.Ques_Type = ?
                                           And (R1.Queue_Id = ? Or R1.Queue_Id Is Null Or R1.Queue_Id = '')
                                         Order By Case
                                                    When R1.Queue_Id = ? And R2.Question = ? Then
                                                     1
                                                    When R1.Queue_Id = ? Then
                                                     2
                                                    When (R1.Queue_Id Is Null Or R1.Queue_Id = '') And R2.Question = ? Then
                                                     3
                                                    When (R1.Queue_Id Is Null Or R1.Queue_Id = '') Then
                                                     4
                                                    Else
                                                     99
                                                  End,
                                                  R2.Createtime Desc Limit 1''';

                    selectResult = jt.queryForList(selectSQL, rclient_Lang, 0, queue_ID, queue_ID, question, queue_ID, question);

                }
                log.debug("Proxy获取本地答案 [Ques_Type:" + Ques_Type + "] DB结果:" + selectResult.toString());

                String Ques_Guid = "";
                String Answer = "";
                if (selectResult.size() > 0) {
                    Ques_Guid = GetMapV(selectResult.get(0), "Ques_Guid");
                    Answer = GetMapV(selectResult.get(0), "Answer");
                    log.debug("Proxy获取本地答案 [Answer]:" + Answer);

                    String defaultInfo = question.startsWith("#问候语#") ? "感谢您联系西卡德高,请输入您的问题?" : "对不起!你输入的问题暂时未找到结果.";

                    ReAnswerInfo = KHUtil.isNull(Answer) ? defaultInfo : Answer;

                    // 如果是因为未找到答案则结果默认
                    if (Ques_Type == -1) {
                        ReAnswerInfo = "对不起!你输入的问题暂时未找到结果.";
                    }
                    // 问题对应答案(如果答案空则默认)
                    ReAnswerInfo = "<p>" + ReAnswerInfo + "</p>";
                    log.debug("Proxy获取本地答案 [ReAnswerInfo]:" + ReAnswerInfo);

                    JSONArray recommendArr = new JSONArray();
                    String recommendSQL = '''Select R1.Ques_Guid, R2.Rec_Guid, R2.Question, R2.Answer, R2.Answer_Lang, R2.Answer_Type, R2.Html_Answer_Path
                                      From Kh_Robot_Question_Recommend R0
                                      Left Join Kh_Robot_Question R1
                                        On R0.Recommend_Ques_Guid = R1.Ques_Guid
                                      Left Join Kh_Robot_Question_Lang R2
                                        On R1.Ques_Guid = R2.Ques_Guid
                                     Where Now() Between R1.Starttime And R1.Endtime
                                       And R2.Answer_Lang = ?
                                       And R0.Ques_Guid = ?
                                     Order By R0.Sort_No''';

                    List<Map<String, Object>> recommendResult = jt.queryForList(recommendSQL, rclient_Lang, Ques_Guid);
                    log.debug("Proxy获取本地推荐 [Ques_Type:" + Ques_Type + "]-关联推荐DB结果:" + recommendResult.toString());

                    // 循环解析推荐问题
                    JSONArray recomArra = new JSONArray();
                    for (int i = 0; i < recommendResult.size(); i++) {
                        JSONObject recomJson = new JSONObject();
                        String re_Question = recommendResult.get(i).get("Question").toString();
                        // recommendArr.add((i + 1) + "-" + re_Question);
                        recomJson.put("question", re_Question);

                        // RecommendInfo = RecommendInfo + "<p><a class=\"send-message list-message\" data-message=\"" + re_Question + "\" style=\"margin-top: 0px;\">" + (i + 1) + "." + encode(re_Question, true) + "</a></p>";
                        recomArra.add(recomJson);

                    }

                    // 初始化resultBotAns的报文
                    resultBotAns = JSONObject.parseObject(resHuman.toString());

                    // 封装机器人答案
                    if (recomArra.size() > 0) {
                        resultBotAns.getJSONObject("data").put("stripe", "请您选择需要咨询的问题：");
                        resultBotAns.getJSONObject("data").put("suggestions", recomArra);
                    } else {
                        if (ReAnswerInfo.indexOf("转人工服务") > -1) {
                            JSONObject recomJson = new JSONObject();
                            recomJson.put("question", "转人工服务");
                            recomArra.add(recomJson);
                            resultBotAns.getJSONObject("data").put("stripe", "请您选择：");
                            resultBotAns.getJSONObject("data").put("suggestions", recomArra);
                        }
                    }

                    // 修改机器人返回
                    resultBotAns.getJSONObject("data").put("answer", ReAnswerInfo);
                    resultBotAns.getJSONObject("data").put("transfer_type", 0);
                    resultBotAns.getJSONObject("data").put("queue_name", "");

                }
            } catch (Exception e) {
                log.error("Proxy获取本地问题答案异常", e);
            }
        }

        log.debug("Proxy获取本地[Ques_Type:" + Ques_Type + "] JSON返回:" + resultBotAns.toString());
        return resultBotAns;

    }

    // 获取本地转人工校验
    static JSONObject getLocalStaff(EliteBean eliteBean, JdbcTemplate jt, String question, String queue_ID, String client_Lang) {
        JSONObject resultBotAns = new JSONObject();
        try {
            // String rclient_Lang = (client_Lang == "zh_CN") ? "简体" : KHDBConfig.getParam(eliteBean, "SYSENV", "").indexOf("-ML") >= 0 ? "简体" : "繁体";
            log.debug("Proxy获取本地转人工校验_是否转人工关键字-语言:" + rclient_Lang + " 问题:" + question + " 队列:" + queue_ID);

            String selectSQL = '''Select R1.Ques_Guid, R2.Rec_Guid, R2.Question, R2.Answer, R2.Answer_Lang, R2.Answer_Type, R2.Html_Answer_Path
                                  From Kh_Robot_Question R1
                                  Left Join Kh_Robot_Question_Lang R2
                                    On R1.Ques_Guid = R2.Ques_Guid
                                 Where Now() Between R1.Starttime And R1.Endtime
                                   And R2.Question Like Concat('%', ?, '%')
                                   And R2.Answer_Lang = ?
                                   And R1.Ques_Type = -1
                                   And (R1.Queue_Id = ? Or R1.Queue_Id Is Null Or R1.Queue_Id = '')
                                   Order By Robot_ID Desc Limit 1''';

            List<Map<String, Object>> selectResult = jt.queryForList(selectSQL, question, rclient_Lang, queue_ID);
            log.debug("Proxy获取本地转人工校验_是否转人工关键字-结果:" + selectResult.toString());

            if (selectResult.size() > 0) {
                resultBotAns = JSONObject.parseObject(resHuman.toString());
                resultBotAns.getJSONObject("data").put("queue_name", "获取本地转人工校验-转人工关键字");

            }
        } catch (Exception e) {
            log.error("Proxy获取本地转人工校验_是否转人工关键字-异常", e);
        }

        return resultBotAns;

    }

    // 快捷话术模式
    static JSONObject doRobotJson(JdbcTemplate jt, JSONObject sobotBody) {

        JSONObject resultBotAns = JSONObject.parseObject(resHuman.toString());

        try {
            // 处理头文件
            String question = sobotBody.getString("question");
            log.debug("调用Bot发送问题：" + question);

            String RecommendInfo = "";


            // 如果是机器人欢迎语
            if (question.equals("#问候语#")) {
                // 机器人欢迎语
                String selectWelcome = '''Select Id, Title, Ques_Type, Use_Times, Display_Order
                                                      From (Select R1.Id, R1.Name As Title, 1 As Ques_Type, Robot_Times As Use_Times, Display_Order
                                                              From Chat_Canned_Message_Group R1
                                                            Union All
                                                            Select R1.Id, R1.Title, 2 As Ques_Type, Ifnull(R3.Use_Times, 0) As Use_Times, R1.Display_Order
                                                              From Chat_Canned_Message R1
                                                              Left Join Chat_Canned_Message_Group R2
                                                                On R1.Parent_Id = R2.Id
                                                              Left Join (Select Id, Count(*) As Use_Times
                                                                          From Chat_Canned_Message_Send
                                                                         Where Send_Date > Date_Add(Now(), Interval 30 Day)
                                                                         Group By Id) R3
                                                                On R1.Id = R3.Id) R1
                                                     Order By Ques_Type, Case When Ques_Type = 1 Then Display_Order Else Use_Times End, Display_Order, ID Limit 6''';
                List<Map<String, Object>> selectWelcomeList = jt.queryForList(selectWelcome);
                log.debug("调用Bot机器人欢迎语 机器人欢迎语结果：" + selectWelcomeList.toString());

                JSONArray recomArra = new JSONArray();
                for (int i = 0; i < selectWelcomeList.size(); i++) {
                    JSONObject recomJson = new JSONObject();
                    Map selectQueRecommendMap = selectWelcomeList.get(i);
                    String Id = selectQueRecommendMap.get("Id").toString();
                    String Title = selectQueRecommendMap.get("Title").toString();

                    recomJson.put("question", Title);
                    recomArra.add(recomJson);

                }

                // 封装机器人答案
                if (recomArra.size() > 0) {
                    resultBotAns.getJSONObject("data").put("stripe", "请您选择需要咨询的问题：");
                    resultBotAns.getJSONObject("data").put("suggestions", recomArra);
                }
                resultBotAns.getJSONObject("data").put("answer", "感谢您联系西卡德高,请输入您的问题?");
                resultBotAns.getJSONObject("data").put("transfer_type", 0);
                resultBotAns.getJSONObject("data").put("queue_name", "");

                return resultBotAns;
            } else {

                // 根据问题查询当前是哪级
                String selectQueLevel = '''Select R1.Id From Chat_Canned_Message_Group R1 Where R1.Name = ? Limit 1''';
                List<Map<String, Object>> selectQueLevelList = jt.queryForList(selectQueLevel, question);
                log.debug("调用Bot发送问题 根据问题查询当前是哪级：" + selectQueLevelList.toString());

                // 查询结果有数据则按导航返回
                if (selectQueLevelList.size() > 0) {

                    Map selectQueLevelMap = selectQueLevelList.get(0);
                    String Level_ID = selectQueLevelMap.get("Id").toString();

                    // 更新记录检索次数
                    jt.update("Update Chat_Canned_Message_Group Set Robot_Times = Robot_Times + 1 Where Id = ?", Level_ID);

                    // 根据问题级别查询下级推荐
                    String selectQueRecommend = '''Select Id, Title, Ques_Type, Use_Times, Display_Order
                                                  From (Select R1.Id, R1.Name As Title, 1 As Ques_Type, Robot_Times As Use_Times, Display_Order
                                                          From Chat_Canned_Message_Group R1
                                                         Where R1.Parent_Id = ?
                                                        Union All
                                                        Select R1.Id, R1.Title, 2 As Ques_Type, Ifnull(R3.Use_Times, 0) As Use_Times, R1.Display_Order
                                                          From Chat_Canned_Message R1
                                                          Left Join Chat_Canned_Message_Group R2
                                                            On R1.Parent_Id = R2.Id
                                                          Left Join (Select Id, Count(*) As Use_Times
                                                                      From Chat_Canned_Message_Send
                                                                     Where Send_Date > Date_Add(Now(), Interval 30 Day)
                                                                     Group By Id) R3
                                                            On R1.Id = R3.Id
                                                         Where R1.Parent_Id = ?) R1
                                                 Order By Ques_Type, Case When Ques_Type = 1 Then Display_Order Else Use_Times End, Display_Order, ID Limit 6''';
                    List<Map<String, Object>> selectQueRecommendList = jt.queryForList(selectQueRecommend, Level_ID, Level_ID);
                    log.debug("调用Bot发送问题 根据问题级别查询下级推荐：" + selectQueRecommendList.toString());

                    JSONArray recomArra = new JSONArray();
                    for (int i = 0; i < selectQueRecommendList.size(); i++) {
                        JSONObject recomJson = new JSONObject();
                        Map selectQueRecommendMap = selectQueRecommendList.get(i);
                        String Id = selectQueRecommendMap.get("Id").toString();
                        String Title = selectQueRecommendMap.get("Title").toString();

                        recomJson.put("question", Title);
                        recomArra.add(recomJson);
                    }

                    // 封装机器人答案
                    if (recomArra.size() > 0) {
                        resultBotAns.getJSONObject("data").put("stripe", "请您选择需要咨询的问题：");
                        resultBotAns.getJSONObject("data").put("suggestions", recomArra);
                    }

                    resultBotAns.getJSONObject("data").put("answer", "<p><strong>请您选择需要咨询的问题：</strong></p>");
                    resultBotAns.getJSONObject("data").put("transfer_type", 0);
                    resultBotAns.getJSONObject("data").put("queue_name", "");

                    return resultBotAns;
                } else {
                    // 如果检索不到导航则检索答案
                    String selectQueAns = '''Select Id, Title, Message_Text, Search_Type, Use_Times, Display_Order
                                                From (Select R1.Id,
                                                        R1.Title,
                                                        R1.Message_Text,
                                                        Case
                                                        When R1.Title Like Concat('%', ?, '%') Then
                                                        1
                                                        Else
                                                        2
                                                        End As Search_Type,
                                                        Ifnull(R3.Use_Times, 0) As Use_Times,
                                                                R1.Display_Order
                                                                From Chat_Canned_Message R1
                                                                Left Join Chat_Canned_Message_Group R2
                                                                On R1.Parent_Id = R2.Id
                                                        Left Join (Select Id, Count(*) As Use_Times
                                                                From Chat_Canned_Message_Send
                                                                Where Send_Date > Date_Add(Now(), Interval 30 Day)
                                                                Group By Id) R3
                                                                On R1.Id = R3.Id
                                                        Where R1.Title Like Concat('%', ?, '%')
                                                                Or R1.Message_Text Like Concat('%', ?, '%')) R1
                                                Order By Search_Type, Use_Times, Display_Order Limit 1''';
                    List<Map<String, Object>> selectQueAnsList = jt.queryForList(selectQueAns, question, question, question);
                    log.debug("调用Bot发送问题 如果检索不到导航则检索答案：" + selectQueAnsList.toString());

                    // 查询结果有数据返回检索答案
                    if (selectQueAnsList.size() > 0) {
                        Map selectQueAnsMap = selectQueAnsList.get(0);
                        String Message_Text = selectQueAnsMap.get("Message_Text").toString();

                        resultBotAns.getJSONObject("data").put("answer", "<p><strong>" + Message_Text + "</strong></p>");
                        resultBotAns.getJSONObject("data").put("transfer_type", 0);
                        resultBotAns.getJSONObject("data").put("queue_name", "");
                        return resultBotAns;

                    } else {

                        // 如果问题无答案返回热点问题
                        String selectQueDefault = '''Select Id, Title, Ques_Type, Use_Times, Display_Order
                                                      From (Select R1.Id, R1.Name As Title, 1 As Ques_Type, Robot_Times As Use_Times, Display_Order
                                                              From Chat_Canned_Message_Group R1
                                                            Union All
                                                            Select R1.Id, R1.Title, 2 As Ques_Type, Ifnull(R3.Use_Times, 0) As Use_Times, R1.Display_Order
                                                              From Chat_Canned_Message R1
                                                              Left Join Chat_Canned_Message_Group R2
                                                                On R1.Parent_Id = R2.Id
                                                              Left Join (Select Id, Count(*) As Use_Times
                                                                          From Chat_Canned_Message_Send
                                                                         Where Send_Date > Date_Add(Now(), Interval 30 Day)
                                                                         Group By Id) R3
                                                                On R1.Id = R3.Id) R1
                                                     Order By Use_Times, Ques_Type, Display_Order Limit 6''';
                        List<Map<String, Object>> selectQueDefaultList = jt.queryForList(selectQueDefault);
                        log.debug("调用Bot发送问题 如果问题无答案返回热点问题：" + selectQueDefaultList.toString());

                        int i = 0;
                        JSONArray recomArra = new JSONArray();

                        for (i = 0; i < selectQueDefaultList.size(); i++) {
                            JSONObject recomJson = new JSONObject();

                            Map selectQueRecommendMap = selectQueDefaultList.get(i);
                            String Id = selectQueRecommendMap.get("Id").toString();
                            String Title = selectQueRecommendMap.get("Title").toString();


                            recomJson.put("question", Title);
                            recomArra.add(recomJson);
                        }

                        JSONObject recomJson = new JSONObject(); recomJson.put("question", "转人工服务");
                        recomArra.add(recomJson);

                        // 封装机器人答案
                        if (recomArra.size() > 0) {
                            resultBotAns.getJSONObject("data").put("stripe", "试试下列问题：");
                            resultBotAns.getJSONObject("data").put("suggestions", recomArra);
                        }

                        resultBotAns.getJSONObject("data").put("answer", "对不起!你输入的问题暂时无结果");
                        resultBotAns.getJSONObject("data").put("transfer_type", 0);
                        resultBotAns.getJSONObject("data").put("queue_name", "");

                        return resultBotAns;

                    }

                }
            }

        } catch (Exception e1) {
            log.error("调用SoBot结果异常：", e1);
            resultBotAns.put("code", "-1");
            resultBotAns.put("msg", e1.getMessage());

        }
        return resultBotAns;

    }

    // 转人工&订单列表&订单详情卡片
    static JSONObject doProductJson(JdbcTemplate jt, JSONObject callJson) {

        JSONObject resultBotAns = JSONObject.parseObject(resHuman.toString());

        try {
            // 处理头文件
            String Customer_Guid = callJson.getString("clientID");
            String question = callJson.getJSONObject("data").getString("question");
            log.debug("转人工&订单列表&订单详情卡片 问题关键字：" + question);

            String RecommendInfo = "";

            // 查询结果有数据返回检索答案
            if (Arrays.asList(humanKeys).contains(question) || question.equals("订单#")) {
                // 客户的近期订单
                String selectProdAns = '''Select Concat('订单#', R2.Order_No) As Product_Id,
                                                   R2.Title As Product_Name,
                                                   Concat('<div class="message-imgtext" data-url="',
                                                          if(R2.Product_Url is null or R2.Product_Url= '',R2.Img,R2.Product_Url),
                                                          '"><div class="title">',
                                                          R2.Order_No,
                                                          '</div><div class="content"><div class="desc"><div class="wrapper">',
                                                          R2.Subtitle,
                                                          '</div></div><img class="thumb" src="',
                                                          R2.Img,
                                                          '"></div></div>') As Product_Html
                                              From Customer R1
                                              Left Join Chat_Product_In R2
                                                On R1.Userid = R2.Customer_Id
                                             Where Status = 1
                                               And R1.Customer_Guid = ?
                                             Order By Createdtime Limit 5''';
                List<Map<String, Object>> selectProdAnsList = jt.queryForList(selectProdAns, Customer_Guid);
                log.debug("转人工&订单列表&订单详情卡片 获取订单列表：" + selectProdAnsList.toString());

                int i = 0;
                JSONArray recomArra = new JSONArray();

                for (i = 0; i < selectProdAnsList.size(); i++) {
                    JSONObject recomJson = new JSONObject();

                    Map selectProdAnsMap = selectProdAnsList.get(i);
                    String product_id = selectProdAnsMap.get("product_id").toString();


                    recomJson.put("question", product_id);
                    recomArra.add(recomJson);
                }

                if (selectProdAnsList.size() > 0) {
                    // 无论转人工还是订单列表获取统一回复


                    JSONObject recomJson = new JSONObject(); recomJson.put("question", "转人工服务");
                    recomArra.add(recomJson);

                    // 封装机器人答案
                    if (recomArra.size() > 0) {
                        resultBotAns.getJSONObject("data").put("stripe", "近期订单：");
                        resultBotAns.getJSONObject("data").put("suggestions", recomArra);
                    }

                    resultBotAns.getJSONObject("data").put("answer", "感谢您联系西卡德高,请问是需要咨询以下订单吗?可以选择订单或直接点击转人工联系我们!");
                    resultBotAns.getJSONObject("data").put("transfer_type", 0);
                    resultBotAns.getJSONObject("data").put("queue_name", "");
                } else {
                    if (Arrays.asList(humanKeys).contains(question)) {
                        // 转人工但无订单列表直接转人工
                        resultBotAns.getJSONObject("data").put("answer", "正在为您转接人工客服,请稍后...");
                    } else {
                        // 咨询订单列表但未查询到订单
                        JSONObject recomJson = new JSONObject(); recomJson.put("question", "转人工服务");
                        recomArra.add(recomJson);

                        // 封装机器人答案
                        if (recomArra.size() > 0) {
                            resultBotAns.getJSONObject("data").put("stripe", "近期订单：");
                            resultBotAns.getJSONObject("data").put("suggestions", recomArra);
                        }

                        resultBotAns.getJSONObject("data").put("answer", "感谢您联系西卡德高,抱歉,暂未找到您近期的订单,您可以直接点击转人工联系我们!");
                        resultBotAns.getJSONObject("data").put("transfer_type", 0);
                        resultBotAns.getJSONObject("data").put("queue_name", "");
                    }

                }
                log.debug("转人工&订单列表&订单详情卡片 返回处理结果：" + resultBotAns.toString());

                return resultBotAns;

            } else if (question.startsWith("订单#")) {
                String product_id = question.replace("订单#", "");
                // 客户的近期订单
                String selectProdAns = '''select order_no as product_id, title as product_name, CONCAT('<div class="message-imgtext" data-url="',ifnull(product_url,''),'"><div class="title">',title,'</div><div class="content"><div class="desc"><div class="wrapper">',order_no,'</div></div><img class="thumb" src="',img,'"></div></div>') as product_html from Chat_Product_In where order_no = ? and Status = 1 order by createdtime desc limit 1''';
                List<Map<String, Object>> selectProdAnsList = jt.queryForList(selectProdAns, product_id);
                log.debug("转人工&订单列表&订单详情卡片 如果检索不到：" + selectProdAnsList.toString());

                for (int i = 0; i < selectProdAnsList.size(); i++) {
                    Map selectProdAnsMap = selectProdAnsList.get(i);
                    String product_html = selectProdAnsMap.get("product_html").toString();

                    RecommendInfo = RecommendInfo + product_html;
                }

                resultBotAns.getJSONObject("data").put("answer", RecommendInfo);
                log.debug("转人工&订单列表&订单详情卡片 返回处理结果：" + resultBotAns.toString());

                return resultBotAns;

            } else {

                log.warn("转人工&订单列表&订单详情卡片 警告：非预期问题-" + question + " 转常规问题处理");
                return doRobotJson(jt, callJson.getJSONObject("data"));
            }

        } catch (Exception e1) {
            log.error("转人工&订单列表&订单详情卡片 异常：", e1);
            resultBotAns.put("code", "-1");
            resultBotAns.put("msg", e1.getMessage());

        }
        return resultBotAns;

    }

    // 工具方法
    static String encode(String input, boolean encode) {
        if (input == null) {
            return "";
        }

        try {
            return encode ? StringEscapeUtils.escapeHtml(input) : StringEscapeUtils.unescapeHtml(input);
        } catch (Exception e) {
            return input;
        }

        return input;
    }

    // 解析Mapn值(此方法增加需要解析的key不存在保护)
    static String GetMapV(Map inMap, String getKey) {
        String Rev_Value = null;
        try {
            Rev_Value = String.valueOf(inMap.get(getKey));
        } catch (Exception e) {

            Rev_Value = "";
        } finally {
            if (Rev_Value == null || Rev_Value.equals("null") || Rev_Value.length() <= 0) {
                Rev_Value = "";
            }
        }
        return Rev_Value;
    }

    // 客户进线队列识别
    static JSONObject doHumanQueue(JdbcTemplate jt, JSONObject callJson, JSONObject sobotBody) throws Exception {

        log.debug("Proxy客户进线队列识别 进线信息callJson:" + callJson.toString() + " 机器人当前返回sobotBody:" + sobotBody.toString());
        String Customer_Guid = callJson.getString("clientID");
        if (!sobotBody.isEmpty() && sobotBody.containsKey("data") && sobotBody.getJSONObject("data").containsKey("transfer_type") && sobotBody.getJSONObject("data").getIntValue("transfer_type") == 99) {

            // 初始化网聊登录信息
            int queue = 3;// 网聊登录queueID
            String queueName = "默认队列";// 网聊登录queueName
            String Rec_Guid = "";//

            KHUtil.GetFSJV(callJson,"")

            callJson.ge

            // 获取聊天请求ID对应的客户进线信息
            String requestID = callJson.getString("requestID");
            String request_ParamSQL = '''Select R2.Rec_Guid,
                                           R2.Sourcesys, -- 来源系统
                                           R2.Region, -- 大区
                                           R2.Organization, -- 销售组织
                                           R2.Salesterritory, -- 销售区域
                                           R2.Usermobile, -- 手机号码
                                           R2.userId, -- 客户编号
                                           R2.userName -- 客户名称
                                      From Chat_Request R1
                                      Left Join Dg_Chat_Inparams R2
                                        On R1.Brand = R2.Rec_Guid
                                     Where R1.Id = ?''';

            List<Map<String, Object>> request_ParamList = jt.queryForList(request_ParamSQL, requestID);
            log.debug("doQueueMain 获取聊天请求ID对应的客户进线信息:" + request_ParamList.toString());

            // 调用参数信息
            /*
            if (request_ParamList.size() > 0) {
                StringBuffer to_Queue_Info = new StringBuffer();
                Rec_Guid = request_ParamList.get(0).get("Rec_Guid") //
                String sourcesys = request_ParamList.get(0).get("Sourcesys") // 来源系统
                String region = request_ParamList.get(0).get("Region"); // 大区
                String sales_org = request_ParamList.get(0).get("Organization"); // 销售组织
                String sales_area = request_ParamList.get(0).get("Salesterritory"); // 销售区域
                String userMobile = request_ParamList.get(0).get("Usermobile"); // 手机号码
                String userName = request_ParamList.get(0).get("userName"); // 客户名称
                log.debug("doQueueMain 获取人工队列入参:" + request_ParamList.get(0).toString());

                String querySQL = "";
                List<Map<String, Object>> querySQLR = null;

                //客户名称匹配
                if (!KHUtil.isNull(userName)) {

                    querySQL = '''SELECT
									d.rules_id,
									d.system_id,
									d.system_name,
									d.area_id,
									d.area_name,
									d.organ_id,
									d.organ_name,
									d.salesterritory_id,
									d.salesterritory_name,
									d.custno,
									d.custname,
									d.mobile,
									d.custtype,
									d.staffstr_id,
									d.staffstr
								FROM
									dg_chat_Incoming_rules d
								 where 1=1
									and d.status=1
									and d.custname = ?
								order by  d.modifydate desc Limit 1''';

                    querySQLR = jt.queryForList(querySQL, userName);
                    log.debug("doQueueMain 获取人工队列规则(直接通过客户名称匹配):" + querySQLR.toString());
                    to_Queue_Info.append(System.lineSeparator() + "获取人工队列规则(直接通过客户名称匹配):" + querySQLR.toString());
                }


                if (querySQLR.size() <= 0 && !KHUtil.isNull(userMobile)) {
                    //2.客户手机号码匹配

                    querySQL = '''SELECT
									d.rules_id,
									d.system_id,
									d.system_name,
									d.area_id,
									d.area_name,
									d.organ_id,
									d.organ_name,
									d.salesterritory_id,
									d.salesterritory_name,
									d.custno,
									d.custname,
									d.mobile,
									d.custtype,
									d.staffstr_id,
									d.staffstr
								FROM
									dg_chat_Incoming_rules d
								 where 1=1
									and d.status=1
									and d.mobile = ?
								order by  d.modifydate desc Limit 1''';

                    querySQLR = jt.queryForList(querySQL, userMobile);
                    log.debug("doQueueMain 获取人工队列规则(直接通过手机号):" + querySQLR.toString());
                    to_Queue_Info.append(System.lineSeparator() + "获取人工队列规则(直接通过手机号):" + querySQLR.toString());
                }
                if (querySQLR.size() <= 0) {
                    //3.如果通过手机号找到规则就不再使用通用规则(来源系统/大区/销售组织)
                    querySQL = '''SELECT
									d.rules_id,
									d.system_id,
									d.system_name,
									d.area_id,
									d.area_name,
									d.organ_id,
									d.organ_name,
									d.salesterritory_id,
									d.salesterritory_name,
									d.custno,
									d.custname,
									d.mobile,
									d.custtype,
									d.staffstr_id,
									d.staffstr
								FROM
									dg_chat_Incoming_rules d
								 where 1=1
									and d.status=1
									and d.system_id = ?
									and d.area_name = ?
									and (d.organ_name = ? Or d.organ_name Is Null Or d.organ_name = '')
									and (d.salesterritory_name = ? Or d.salesterritory_name Is Null Or d.salesterritory_name = '')
								 Order By Case
									When d.area_name Is Not Null And d.organ_name Is Not Null And d.salesterritory_name Is Not Null Then
									 1
									When d.area_name Is Not Null And d.organ_name Is Not Null Then
									 2
									When d.area_name Is Not Null Then
									 3
									Else
									 99
								  End,d.modifydate Limit 1''';

                    querySQLR = jt.queryForList(querySQL, sourcesys, region, sales_org, sales_area);
                    log.debug("doQueueMain 获取人工队列规则(通用规则):" + querySQLR.toString());
                    to_Queue_Info.append(System.lineSeparator() + "获取人工队列规则(通用规则):" + querySQLR.toString());
                }

                // 解析规则获取队列
                String staffstr_id = "";
                String QueueId = "";

                //检查规则中的坐席是否在线
                if (querySQLR.size() > 0) {
                    //获取规则中的坐席
                    staffstr_id = querySQLR.get(0).get("staffstr_id");
                    //查找规则内的坐席属于哪个专属队列并且在线
                    String checkSQL = '''select q.id as QueueId,cca.id as Agent_Id,USER_STATUS as chatstatus from queue q
                                            left join property_value qv on qv.PROPERTY_KEY=q.ID
                                            left join Agent_Queue aq on aq.queue_id=q.id
                                            left join Chat_Cache_Agentbyidmap cca on cca.ID=aq.agent_id
                                            where FIND_IN_SET(cca.ID,?)
                                            and PROPERTY_ID=1035
                                            and qv.value=1
                                            order by USER_STATUS desc''';
                    List<Map<String, Object>> checkSQLR = jt.queryForList(checkSQL, staffstr_id);
                    log.debug("检查规则对应队列是否有在线坐席(登录)-SQL" + ":" + checkSQLR.toString());
                    to_Queue_Info.append(System.lineSeparator() + "检查规则对应队列是否有在线坐席(登录)-LIST" + ":" + checkSQLR.toString());
                    String Agent_Id = "";
                    if (checkSQLR.size() > 0) {
                        Agent_Id = checkSQLR.get(0).get("Agent_Id");// 在线坐席
                        QueueId = checkSQLR.get(0).get("QueueId");// 在线队列
                        log.debug("规则内在线坐席第一位" + ":" + QueueId + "" + Agent_Id);

                        String InsertRouteSQL = '''insert into chat_multichannel_route(ROUTE_GUID,CUSTOMER_GUID,OWNERSTAFF_ID,LASTUPDATEINFOTIME)values(UUID(),?,?,now())''';
                        String InsertSessionSQL = '''INSERT INTO davco_db.chat_session ( LAST_AGENT_ID, PERM_FLAG, CLIENT_ID, AGENT_ID, LAST_QUEUE_ID, PERM_INFO,START_TIME) VALUES ( ?, 1,?, ?, 2250, '{\"update\":false,\"queue\":0,\"sessionType\":0}',?);
''';
                        int ins_Num1 = jt.update(InsertRouteSQL, Customer_Guid,Agent_Id);
                        int ins_Num2 = jt.update(InsertSessionSQL, Agent_Id,Customer_Guid,Agent_Id,System.currentTimeMillis());
                    } else {
                        QueueId = 2251;// 在线队列
                    }

                    queue = QueueId.toInteger();
                }


                log.debug("doQueueMain 获取人工队列结果[queue]:" + queue + "[queueName]:" + queueName);
                to_Queue_Info.append(System.lineSeparator() + "获取人工队列结果[queue]:" + queue + "[queueName]:" + queueName);
                sobotBody.getJSONObject("data").put("queue_id", queue);
                sobotBody.getJSONObject("data").put("queue_name", queueName);

                if (!KHUtil.isNull(Rec_Guid)) {
                    jt.update("update dg_chat_inparams set to_queue_info = ? where rec_guid = ?", to_Queue_Info, Rec_Guid);
                }

            }
            */

        } else {
            log.debug("Proxy客户进线队列识别 机器人当前sobotBody无转接人工信息,跳过队列识别处理...");

        }

        if (!sobotBody.isEmpty()) {
            //因无法从机器人转入专属坐席，故机器人转人工时，只当做默认人工队列
            sobotBody.getJSONObject("data").put("queue_id", 3);
            sobotBody.getJSONObject("data").put("queue_name", "人工队列");
        }
        log.debug("Proxy客户进线队列识别 机器人最后返回sobotBody:" + sobotBody);
        return sobotBody;

    }
}
